package com.example.myutils.excel;

import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;

import static com.example.myutils.excel.Export.getCellFormatValue;

public class Main {
    public static void main(String[] args) throws IOException {
        Main main = new Main();
        main.read();
    }

    public void read() throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("D:/检测模板2.xlsx"));
        //读取了模板内所有sheet内容
        XSSFSheet sheet = wb.getSheetAt(0);
        for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//获取总行数
            Row row = sheet.getRow(j); // 取出第i行 getRow(index) 获取第(j)行
            if (row == null) {
                continue;
            }
            for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) { // getPhysicalNumberOfCells() 获取当前行的总列数
                String value1 = getCellFormatValue(row.getCell(k));//取出第j行第k列的值
                System.out.print("" + j + "," + k + "" + ": " + value1 + "。");
            }
            System.out.println();
        }
    }

    public void exportExcel() throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("D:/检测模板2.xlsx"));
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFCell c1_2 = sheet.getRow(1).getCell(2);
        XSSFCell c9_15 = sheet.getRow(9).getCell(15);
        XSSFCell c31_2 = sheet.getRow(31).getCell(2);
        XSSFCell c51_2 = sheet.getRow(51).getCell(2);
        XSSFCell c78_2 = sheet.getRow(78).getCell(2);
        XSSFCell c106_2 = sheet.getRow(106).getCell(2);
        c1_2.setCellValue("报告编号");
        c9_15.setCellValue("报告编号");
        c31_2.setCellValue("报告编号");
        c51_2.setCellValue("报告编号");
        c78_2.setCellValue("报告编号");
        c106_2.setCellValue("报告编号");
        XSSFCell c16_10 = sheet.getRow(16).getCell(10);
        c16_10.setCellValue("项目名称");
        XSSFCell c17_10 = sheet.getRow(17).getCell(10);
        c17_10.setCellValue("委托单位");
        XSSFCell c18_10 = sheet.getRow(18).getCell(10);
        c18_10.setCellValue("检测类别");
        XSSFCell c19_10 = sheet.getRow(19).getCell(10);
        c19_10.setCellValue("报告日期");
        XSSFCell c54_2 = sheet.getRow(54).getCell(2);
        c54_2.setCellValue("    前言");
        XSSFCell c59_2 = sheet.getRow(59).getCell(2);
        XSSFCell c59_7 = sheet.getRow(59).getCell(7);
        XSSFCell c59_18 = sheet.getRow(59).getCell(18);
        XSSFCell c59_24 = sheet.getRow(59).getCell(24);
        c59_2.setCellValue("检测项目");
        c59_7.setCellValue("方法名称");
        c59_18.setCellValue("方法编号");
        c59_24.setCellValue("检测方法");
        XSSFCell c63_10 = sheet.getRow(63).getCell(10);
        XSSFCell c63_23 = sheet.getRow(63).getCell(23);
        c63_10.setCellValue("型号");
        c63_23.setCellValue("鉴定证书编号");
        XSSFCell c66_8 = sheet.getRow(66).getCell(8);
        XSSFCell c66_23 = sheet.getRow(66).getCell(23);
        c66_8.setCellValue("机械类型/名称");
        c66_23.setCellValue("机械型号");
        XSSFCell c67_8 = sheet.getRow(67).getCell(8);
        XSSFCell c67_23 = sheet.getRow(67).getCell(23);
        c67_8.setCellValue("序列号");
        c67_23.setCellValue("环保编号");
        XSSFCell c68_8 = sheet.getRow(68).getCell(8);
        XSSFCell c68_23 = sheet.getRow(68).getCell(23);
        c68_8.setCellValue("机械环保号牌");
        c68_23.setCellValue("设备牌号");
        XSSFCell c69_9 = sheet.getRow(69).getCell(8);
        XSSFCell c69_23 = sheet.getRow(69).getCell(23);
        c69_9.setCellValue("排放阶段");
        c69_23.setCellValue("出厂日期");
        XSSFCell c70_8 = sheet.getRow(70).getCell(8);
        XSSFCell c70_23 = sheet.getRow(70).getCell(23);
        c70_8.setCellValue("机械VIN码");
        c70_23.setCellValue("生产企业名称  ");
        XSSFCell c71_8 = sheet.getRow(71).getCell(8);
        XSSFCell c71_23 = sheet.getRow(71).getCell(23);
        c71_8.setCellValue("发动机型号");
        c71_23.setCellValue("发动机编号");
        XSSFCell c72_8 = sheet.getRow(72).getCell(8);
        XSSFCell c72_23 = sheet.getRow(72).getCell(23);
        c72_8.setCellValue("发动机制造厂");
        c72_23.setCellValue("发动机后处理类型");
        XSSFCell c73_8 = sheet.getRow(73).getCell(8);
        XSSFCell c73_23 = sheet.getRow(73).getCell(23);
        c73_8.setCellValue("发动机额定转速");
        c73_23.setCellValue("发动机额定净功率");
        XSSFCell c74_8 = sheet.getRow(74).getCell(8);
        XSSFCell c74_23 = sheet.getRow(74).getCell(23);
        c74_8.setCellValue("环保信息公开编号");
        c74_23.setCellValue("发动机型式核准号");
        XSSFCell c81_8 = sheet.getRow(81).getCell(8);
        c81_8.setCellValue("姓名");
        XSSFCell c82_8 = sheet.getRow(82).getCell(8);
        c82_8.setCellValue("联系电话");
        XSSFCell c83_8 = sheet.getRow(83).getCell(8);
        c83_8.setCellValue("联系地址");
        XSSFCell c86_6 = sheet.getRow(86).getCell(6);
        XSSFCell c86_14 = sheet.getRow(86).getCell(14);
        XSSFCell c86_21 = sheet.getRow(86).getCell(21);
        XSSFCell c86_28 = sheet.getRow(86).getCell(28);
        c86_6.setCellValue("温度");
        c86_14.setCellValue("大气压");
        c86_21.setCellValue("相对湿度");
        c86_28.setCellValue("海拔高度");
        XSSFCell c90_9 = sheet.getRow(90).getCell(9);
        XSSFCell c90_24 = sheet.getRow(90).getCell(24);
        c90_9.setCellValue("检测日期");
        c90_24.setCellValue("检测起止时间");
        XSSFCell c92_11 = sheet.getRow(92).getCell(11);
        XSSFCell c92_16 = sheet.getRow(92).getCell(16);
        XSSFCell c92_21 = sheet.getRow(92).getCell(21);
        XSSFCell c92_26 = sheet.getRow(92).getCell(26);
        c92_11.setCellValue("平均值");
        c92_16.setCellValue("转速");
        c92_21.setCellValue("禁止高排区域");
        c92_26.setCellValue("限值");
        XSSFCell c93_2 = sheet.getRow(93).getCell(2);
        XSSFCell c93_5 = sheet.getRow(93).getCell(5);
        XSSFCell c93_8 = sheet.getRow(93).getCell(8);
        c93_2.setCellValue("测量值1");
        c93_5.setCellValue("测量值2");
        c93_8.setCellValue("测量值3");
        XSSFCell c94_11 = sheet.getRow(94).getCell(11);
        c94_11.setCellValue("目视烟羽");
        XSSFCell c96_11 = sheet.getRow(96).getCell(11);
        c96_11.setCellValue("合格判定");
        // 3 109 15 119     ->  18  109  30 119
        // 3 121 15 131     ->  18  121  30 131
        // 3 133 15 143     ->  18  133  30 143
        // 3 145 15 155     ->  18  145  30 155
        // 3 157 15 167     ->  18  157  30 167
        insertPicCost(wb, sheet, "D:/images19.png",3,109,15,119 );
        insertPicCost(wb, sheet, "D:/images19.png",18,109,30,119 );
        insertPicCost(wb, sheet, "D:/images19.png",3,121,15,131 );
        insertPicCost(wb, sheet, "D:/images19.png",18,121,30,131 );
        insertPicCost(wb, sheet, "D:/images19.png",3,133,15,143 );
        insertPicCost(wb, sheet, "D:/images19.png",18,133,30,143 );
        insertPicCost(wb, sheet, "D:/images19.png",3,145,15,155 );
        insertPicCost(wb, sheet, "D:/images19.png",18,145,30,155 );
        insertPicCost(wb, sheet, "D:/images19.png",3,157,15,167 );
        insertPicCost(wb, sheet, "D:/images19.png",18,157,30,167 );
        FileOutputStream out = new FileOutputStream("D:/export2.xlsx");
        wb.write(out);
        out.close();
    }


    public static void insertPicCost(XSSFWorkbook workBook, XSSFSheet topPic, String pic, int x1, int x2, int y1, int y2) {
        BufferedImage bufferImg = null;//图片
        try {
            // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            //将图片读到BufferedImage
            bufferImg = ImageIO.read(new File(pic));
            // 将图片写入流中
            ImageIO.write(bufferImg, "png", byteArrayOut);
            // 利用HSSFPatriarch将图片写入EXCEL
            Drawing patriarch = topPic.createDrawingPatriarch();
            //图片一导出到单元格B2中
            //46 0 56 7
            //前两个是起始位置 , 后两个是图片图片的宽 高
//            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,
//                    (short) 0, 46, (short) 8, 57);
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,
                    (short) x1, x2, (short) y1, y2);
            // 插入图片
            patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut
                    .toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
        } catch (IOException io) {
            System.out.println("插入失败 : " + io.getMessage());
        } finally {

        }
    }
}
